package net.core.service.file;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ExcelParse {
	private static final Log logger = LogFactory.getLog(ExcelParse.class);
	String msg;
	private ImportInfo ii;

	public ExcelParse() {
		ii = new ImportInfo();
	}

	/**
	 * 解析文件并返回有效的记录
	 * 
	 * @param file
	 * @return
	 * @throws ResourceException
	 */
	public List<String[]> parse(String file, String rule) {
		Workbook book = null;
		try {
			logger.info("开始解析资源: " + file + ";");
			book = Workbook.getWorkbook(new File(file));
			// 读取记录
			List<String[]> list = getRecords(book.getSheet(0));
			// 解析符合规则的资源
			list = parseWithRule(list, rule);
			logger.info("符合规则的记录条数: " + list.size() + ";");
			if (list.size() == 0) {
				msg = "没有符合规则的记录";
				logger.error(msg);
			}
			// 去除重复的资源
			list = filterDuplArray(list);
			// 大于5000条的记录不允许导入
			if (list.size() > 5000) {
				msg = "导入的资源记录数量大于5000条";
				logger.error(msg);
			}
			logger.info("有效记录: " + list.size());
			book.close();
			return list;
		} catch (BiffException e) {
			msg = "读取excel文件出错 ";
			logger.error(msg);
			return null;
		} catch (IOException e) {
			msg = "系统无法找到文件 ";
			logger.error(msg);
			return null;
		} finally {
			book.close();
		}
	}

	/**
	 * 依据正则表达式规则过滤数据
	 * 
	 * @param list
	 * @param rule
	 * @return
	 */
	private List<String[]> parseWithRule(List<String[]> list, String rule) {
		List<String[]> result = new ArrayList<String[]>();
		for (Iterator<String[]> it = list.iterator(); it.hasNext();) {
			String[] detail = it.next();
			// 判断有效数据
			Pattern pattern = Pattern.compile(rule);
			Matcher matcher = pattern.matcher(detail[0]);
			if (matcher.matches()) {
				result.add(detail);
			}
		}
		return result;
	}

	/**
	 * 获取所有记录
	 * 
	 * @param sheet
	 * @return
	 */
	private List<String[]> getRecords(Sheet sheet) {
		List<String[]> list = new ArrayList<String[]>();
		int columns = ii.getColumnsNum();
		String[] detail;
		// 正式数据从第4行开始算
		for (int i = 2; i < sheet.getRows(); i++) {
			detail = new String[columns];
			// 从第一列开始算
			boolean bool = false;
			for (int j = 0; j < columns; j++) {
				CellType type = sheet.getCell(j, i).getType();
				if (type.equals(CellType.DATE)) {
					// 日期 类型的处理
					DateCell dc = (DateCell) sheet.getCell(j, i);
					Date jxlDate = dc.getDate();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
					detail[j]=sdf.format(jxlDate);
				}else{
					detail[j] = sheet.getCell(j, i).getContents().trim();
				}
				// 过滤为空的记录,只要有一个不为空的，就OK
				if (!"".equals(detail[j])) {
					bool = true;
				}
			}
			if (bool) {
				list.add(detail);
			}
		}
		return list;
	}

	/**
	 * 筛除重复的记录
	 * 
	 * @param slist
	 * @return
	 */
	public List<String[]> filterDuplArray(List<String[]> slist) {
		List<String[]> tlist = new ArrayList<String[]>();
		Map<String, String[]> tm = new HashMap<String, String[]>();

		for (Iterator<String[]> it = slist.iterator(); it.hasNext();) {
			String[] s = it.next();
			tm.put(s[0], s);
		}
		Collection<String[]> c = tm.values();
		for (Iterator<String[]> it = c.iterator(); it.hasNext();) {
			tlist.add(it.next());
		}
		return tlist;
	}

	public ImportInfo parseModule(String file) {
		Workbook book = null;
		try {
			logger.info("开始解析资源: " + file + ";");
			book = Workbook.getWorkbook(new File(file));
			// 读取表头信息
			parseSheet(book.getSheet(0));
			book.close();
			return ii;
		} catch (BiffException e) {
			msg = "读取excel文件出错 ";
			logger.error(msg);
			return null;
		} catch (IOException e) {
			msg = "系统无法找到文件 ";
			logger.error(msg);
			return null;
		} finally {
			book.close();
		}
	}

	public ImportInfo parseModuleInfo(String file) {
		Workbook book = null;
		try {
			logger.info("开始解析资源: " + file + ";");
			book = Workbook.getWorkbook(new File(file));
			// 读取表头信息
			parseSheetInfo(book.getSheet(0));
			book.close();
			return ii;
		} catch (BiffException e) {
			msg = "读取excel文件出错 ";
			logger.error(msg);
			return null;
		} catch (IOException e) {
			msg = "系统无法找到文件 ";
			logger.error(msg);
			return null;
		} finally {
			book.close();
		}
	}

	private void parseSheetInfo(Sheet sheet) {
		ii.setId(sheet.getCell(1, 0).getContents().trim());
		ii.setTableName(sheet.getCell(3, 0).getContents().toUpperCase());
	}

	private void parseSheet(Sheet sheet) {
		ii.setId(sheet.getCell(1, 0).getContents().trim());
		ii.setTableName(sheet.getCell(3, 0).getContents().toUpperCase());
		ii.setColumnsNum(Integer.valueOf(sheet.getCell(5, 0).getContents()));
		// 字段名称变更为从数据库中获取 dum 20130228
		// 获取中文字段名称
		// String[] columnsChs = getRowValue(sheet, 1);
		// ii.setColumnsCHS(columnsChs);

		// 获取英文字段名称
		// String[] columnsEng = getRowValue(sheet, 2);
		// ii.setColumnsENG(columnsEng);
		List<String[]> list = getRecords(sheet);
		ii.setList(list);
	}

	private String[] getRowValue(Sheet sheet, int i) {
		Cell[] cells = sheet.getRow(i);
		String[] results = new String[ii.getColumnsNum()];
		for (int k = 0; k < ii.getColumnsNum(); k++) {
			results[k] = cells[k].getContents();
		}
		return results;
	}

	public ImportInfo getIi() {
		return ii;
	}

	public void setIi(ImportInfo ii) {
		this.ii = ii;
	}

}
